package com.yidian.dao;

import com.yidian.pojo.order.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

/**
 * @author whb
 * @create 2021-06-02 15:38
 * @desciption 商品类目统计Mapper
 */
public interface CategoryReportMapper extends Mapper<CategoryReport> {
    /**
     * 按日期查询商品类目统计数据
     * @param date
     * @return
     */
    @Select("SELECT " +
            "category_id1 categoryId1,category_id2 categoryId2,category_id3 categoryId3,DATE_FORMAT(o.pay_time,'%Y-%m-%d') countDate,SUM(o1.num) num,SUM(o1.pay_money) money " +
            "FROM " +
            "tb_order_item o1,tb_order o " +
            "WHERE " +
            "o1.order_id=o.id " +
            "AND o.pay_status='1' " +
            "AND DATE_FORMAT(o.pay_time,'%Y-%m-%d')=#{date} " +
            "GROUP BY " +
            "category_id1,category_id2,category_id3,DATE_FORMAT(o.pay_time,'%Y-%m-%d')")
    public List<CategoryReport> categoryReport(@Param("date") LocalDate date);

    /**
     * 按日期查询统计数据（商品一级类目）
     * @param beginDate
     * @param endDate
     * @return
     */
    @Select("SELECT " +
            "r.category_id1 categoryId1,c.name categoryName,SUM(num) num,SUM(money) money " +
            "FROM " +
            "tb_category_report r,v_categoryParentId1 c " +
            "WHERE " +
            "r.category_id1=c.id " +
            "AND count_date>=#{beginDate} " +
            "AND count_date<=#{endDate} " +
            "GROUP BY r.category_id1")
    public List<Map> categoryId1Count(@Param("beginDate") String beginDate,@Param("endDate") String endDate);
}
